﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.Xml;

namespace DataLayerClass
{
    public static class DataConnection
    {
        /// <summary>
        /// Makes a connection to the database and runs the given query.
        /// </summary>
        /// <param name="query">Query to execute.</param>
        /// <returns>The resultset of the query.</returns>
        private static DataSet executeQuery(string query, string connStr)
        {
            SqlConnection conn = new SqlConnection();
            SqlDataAdapter adp = new SqlDataAdapter();
            DataSet ds = new DataSet();

            conn.ConnectionString = connStr;
            adp = new SqlDataAdapter(query, conn);
            adp.Fill(ds);
            conn.Close();
            return ds;
        }

        /// <summary>
        /// Fetches all customerIDs from the database and returns them in a dataset.
        /// </summary>
        /// <returns>The dataset containing all customerIDs.</returns>
        public static DataSet getAllCustomers(string connStr)
        {
            return executeQuery(getQueryAllCustomers(), connStr);
        }

        /// <summary>
        /// Fetches all products per customer and returns them in a dataset.
        /// </summary>
        /// <param name="level">The role of the user who requested the report.</param>
        /// <param name="customerID">The id of the customer to fetch the products from.</param>
        /// <returns>The dataset containing all ordered products of the customer.</returns>
        public static DataSet getProductsPerCustomer(int level, int customerID, string connStr)
        {
            return executeQuery(getQueryProductsPerCustomer(level, customerID), connStr);
        }

        private static string getQueryProductsPerCustomer(int level, int customerID)
        {
            string query = getQuery("ProductsPerCustomer", level.ToString());
            return query.Replace("thisWillBeReplacedByCustomerID", customerID.ToString());
        }

        private static string getQueryAllCustomers()
        {
            return getQuery("AllCustomers");
        }
        private static string getQuery(string name)
        {
            return getQuery(name, "");
        }

        /// <summary>
        /// Reads the xml file containing all queries for the webservice and returns the query for the give report depending on the user role.
        /// </summary>
        /// <param name="name">Name of the report</param>
        /// <param name="level">The role of the user who requested the report.</param>
        /// <returns>The query in string format for the requested report.</returns>
        private static string getQuery(string name, string level)
        {
            string result = "";
            XmlDocument xmldoc = new XmlDocument();
            xmldoc.Load(@"C:\Users\Joël\School\Project\I-6.8\SVN\trunk\application\source\AWC\DataLayerClass\queries.xml");
            XmlNodeList nodes = xmldoc.GetElementsByTagName("queries");
            XmlNodeList queries = nodes[0].ChildNodes;
            foreach (XmlNode query in queries)
            {
                if (query.Attributes["name"].Value.Equals(name) && query.Attributes["level"].Value.Equals(level))
                {
                    result = query.InnerText;
                }
            }
            return result;
        }
    }
}
